using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Generic;

/// <summary>
/// Summary description for SpotCategoryDAO
/// </summary>
public class SpotCategoryDAO:DBAccess
{
	#region sql Commands   
    string SQL_SELECT_ALL = "select * from SpotCategory where Name like @name";
    string SQL_SELECT_BY_ID = "select * from SpotCategory where ID=@id";
    string SQL_INSERT = "insert into SpotCategory(Name,Description,Article,State, ModifiedDate, ModifiedBy) values(@Name,@Description,@Article,@State,@ModifiedDate,@ModifiedBy)";
    string SQL_UPDATE = "update SpotCategory set Name = @Name, Description = @Description, Article = @Article,State=@State,ModifiedDate=@ModifiedDate,ModifiedBy=@ModifiedBy where ID = @id";
    string SQL_DELETE = "delete from SpotCategory where ID = @id";
    #endregion

    #region fields
    string message = "";
    #endregion

    #region constructors
    public SpotCategoryDAO()
    {

    }
    #endregion

    #region properties
    public string Message
    {
        get { return message; }
        set { message = value; }
    }
    #endregion

    #region Methos
    /// <summary>
    /// Get SpotCategory by id
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public SpotCategory GetSpotCategory(int id)
    {
        SpotCategory spCate = new SpotCategory();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_BY_ID, conn);
            if (Connect())
            {
                command.Parameters.AddWithValue("@id", id);
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    spCate.Id = dr.GetInt32(0);
                    spCate.Name = dr.GetString(1);
                    spCate.Description = dr.GetString(2);
                    spCate.Article = dr.GetString(3);
                    spCate.State = dr.GetBoolean(4);
                    spCate.ModifiedDate = dr.GetDateTime(5);
                    spCate.ModifiedBy = dr.GetInt32(6);
                   
                }
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return spCate;
    }
    /// <summary>
    /// Get all SpotCategories 
    /// </summary>
    /// <returns></returns>
    public IList<SpotCategory> GetAllSpotCategories(string name)
    {
        IList<SpotCategory> list = new List<SpotCategory>();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_ALL, conn);
            command.Parameters.AddWithValue("@name","%"+name+"%");
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    SpotCategory spCate = new SpotCategory();
                    spCate.Id = dr.GetInt32(0);
                    spCate.Name = dr.GetString(1);
                    spCate.Description = dr.GetString(2);
                    spCate.Article = dr.GetString(3);
                    spCate.State = dr.GetBoolean(4);
                    list.Add(spCate);
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }
   /// <summary>
    /// Insert new SpotCategory into database
   /// </summary>
   /// <param name="per"></param>
   /// <returns></returns>
    public int Insert(SpotCategory spCate)
   {
       int effectedRows = 0;
       try
       {
           SqlCommand command = new SqlCommand(SQL_INSERT, conn);
           command.Parameters.AddWithValue("@Name", spCate.Name);
           command.Parameters.AddWithValue("@Description", spCate.Description);
           command.Parameters.AddWithValue("@Article", spCate.Article);
           command.Parameters.AddWithValue("@State", spCate.State);
           command.Parameters.AddWithValue("@ModifiedDate", spCate.ModifiedDate);
           command.Parameters.AddWithValue("@ModifiedBy", spCate.ModifiedBy);          
           if (Connect())
           {
               effectedRows = command.ExecuteNonQuery();
               command.CommandText = "SELECT @@IDENTITY from SpotCategory";
               spCate.Id = int.Parse(command.ExecuteScalar().ToString());
               Disconnect();
           }
       }
       catch (SqlException ex) { message = ex.Message; }
       catch (Exception ex) { message = ex.Message; }
       return effectedRows;
   }

   /// <summary>
   /// Update SpotCategory
   /// </summary>
   /// <param name="per"></param>
   /// <returns></returns>
    public int Update(SpotCategory spCate)
   {
       int effectedRows = 0;
       try
       {
           SqlCommand command = new SqlCommand(SQL_UPDATE, conn);
           command.Parameters.AddWithValue("@Name", spCate.Name);
           command.Parameters.AddWithValue("@Description", spCate.Description);
           command.Parameters.AddWithValue("@Article", spCate.Article);
           command.Parameters.AddWithValue("@State", spCate.State);
           command.Parameters.AddWithValue("@ModifiedDate", spCate.ModifiedDate);
           command.Parameters.AddWithValue("@ModifiedBy", spCate.ModifiedBy);          
           command.Parameters.AddWithValue("@id", spCate.Id);           
           if (Connect())
           {
               effectedRows = command.ExecuteNonQuery();
               Disconnect();
           }
       }
       catch (SqlException ex) { message = ex.Message; }
       catch (Exception ex) { message = ex.Message; }
       return effectedRows;
   }

   /// <summary>
   /// Remove SpotCategory out of database
   /// </summary>
   /// <param name="per"></param>
   /// <returns></returns>
    public int Delete(int Id)
   {
       int effectedRows = 0;
       try
       {
           SqlCommand command = new SqlCommand(SQL_DELETE, conn);
           command.Parameters.AddWithValue("@id", Id);
           if (Connect())
           {
               effectedRows = command.ExecuteNonQuery();
               Disconnect();
           }
       }
       catch (SqlException ex) { message = ex.Message; }
       catch (Exception ex) { message = ex.Message; }
       return effectedRows;
   }   
    #endregion
}
